package Model.Util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Model.Bean.Phone;
import Model.DbAccess.DbConnection;

public class PhoneModel {
	
	DbConnection db = new DbConnection();
	
	/*
	 * Show Phone filter by ...
	 */
	public Phone ShowPhone(int ID)
	{
		String sql = "Select * from tbPhone where PhoneID = ?";
		Object[] params = new Object[] {ID};
		db.makeConnection();
		ResultSet rs = db.executeQuery(sql, params);
		Phone phone = null;
		try{
			if(rs.next()) {
				 phone = new Phone();
				 phone.setPhoneID(rs.getInt("PhoneID"));
				 phone.setPhoneName(rs.getString("PhoneName"));
				 phone.setPhoneType(rs.getInt("PhoneType"));
				 phone.setDescription(rs.getString("Description"));
				 phone.setPicture(rs.getString("Picture"));
				 phone.setPrice(rs.getDouble("Price"));
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			db.closeConnection();
		}
		
		return phone;
	}
	/*
	 * Show Phone filter by ...
	 */
	public List<Phone> ShowPhone(String phoneName)
	{
		db.makeConnection();
		String sql = "Select * from tbPhone where PhoneName like '%?%' Order by CreatedDate desc";
		Object[] params = new Object[] {phoneName};
		ResultSet rs = db.executeQuery(sql, params);
		List<Phone> phones = new ArrayList<Phone>();
		try{
			while(rs.next()) {
				Phone phone = new Phone(
					rs.getInt(0),
					rs.getString(1),
					rs.getInt(2),
					rs.getString(3),
					rs.getDate(4),
					rs.getDouble(5),
					rs.getString(6)
				);
				
				phones.add(phone);
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			db.closeConnection();
		}
		
		return phones;
	}
	/*
	 * Show Phone filter by ...
	 */
	public ArrayList<Phone> ShowPhone(String phoneName, String description, int phoneType) throws SQLException 
	{
		String sql = "";
		Object[] params;
		
		if(phoneType == -1) {
			sql = "Select * from tbPhone where PhoneName like '%?%' and Description like '%?% Order by CreatedDate desc'";
		
			params = new Object[] {
				phoneName, description
			};
		}
		else {
			sql = "Select * from tbPhone where PhoneName like '%?%' and Description like '%?%' and PhoneType = ? Order by CreatedDate desc";
			
			params = new Object[] {
				phoneName, description, phoneType
			};
		}
		
		ResultSet rs = db.executeQuery(sql, params);
		
		ArrayList<Phone> phones = new ArrayList<Phone>();
		
		while(rs.next()) {
			Phone phone = new Phone(
				rs.getInt(0),
				rs.getString(1),
				rs.getInt(2),
				rs.getString(3),
				rs.getDate(4),
				rs.getDouble(5),
				rs.getString(6)
			);
			
			phones.add(phone);
		}
		
		return phones;
	}
	
	
	/*
	 * Show All Phone
	 */
	public List<Phone> ShowAllPhone() {
		List<Phone> phones = new ArrayList<Phone>();
		Phone p ;
		String sql = "SELECT * FROM tbPhone";
		db.makeConnection();
		ResultSet rs = db.executeQuery(sql);
		try {
			
			while(rs.next()) {
				p=new Phone();
				p.setPhoneID(rs.getInt("PhoneID"));
				p.setPhoneName(rs.getString("PhoneName"));
				p.setPhoneType(rs.getInt("PhoneType"));
				p.setDescription(rs.getString("Description"));
				p.setPicture(rs.getString("Picture"));
				//p.setCreatedDate(createdDate);
				p.setPrice(rs.getDouble("Price"));
				phones.add(p);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			//if (rs!=null) try { rs.close(); } catch (SQLException ignore) {}
			db.closeConnection();
			
		}
		return phones;
	}

	
	/*
	 * Show All Phone by Type
	 */
	public ArrayList<Phone> ShowAllPhoneByType(int phoneType) throws SQLException {
		return ShowPhone("","",phoneType);
	}

	
	/*
	 * Search by Phone's Name
	 */
	public ArrayList<Phone> SearchByPhoneName(String phoneName) throws SQLException {
		return ShowPhone(phoneName,"",-1);
	}

	
	/*
	 * Search by Phone's ID
	 */
	public Phone SearchByPhoneID(int phoneID) throws SQLException {
		String sql = "Select * from tbPhone where PhoneID = "+phoneID;
		
		ResultSet rs = db.executeQuery(sql);
		
		Phone phone;
		if(rs.next()) {
			phone = new Phone(
					rs.getInt(0),
					rs.getString(1),
					rs.getInt(2),
					rs.getString(3),
					rs.getDate(4),
					rs.getDouble(5),
					rs.getString(6)
			);
			
			return phone;
		}
		else {
			return null;
		}
	}

}
